package com.atguigu.base;

import org.junit.Test;

import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;

/**
 * @author lhl
 * @date 2025/4/11 17:40
 */
public class JDBCOperation {

    @Test
    public void querySingleRowAndCol() throws Exception{
        //1.注册驱动

        //2.获取连接对象
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/atguigu", "root", "123456");

        //3.获取执行SQL语句的对象
        PreparedStatement preparedStatement = connection.prepareStatement("select count(*) from t_emp ");

        //4.执行,接收返回结果集
        ResultSet resultSet = preparedStatement.executeQuery();

        //5.处理结果集
        while (resultSet.next()){
            int count = resultSet.getInt(1);
            System.out.println(count);
        }

        //6.释放资源（先开后关）
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }

    @Test
    public void querySingleRow() throws Exception{
        //1.注册驱动

        //2.获取连接对象
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/atguigu", "root", "123456");

        //3.获取执行SQL语句的对象
        PreparedStatement preparedStatement = connection.prepareStatement("select emp_id,emp_name,emp_salary,emp_age from t_emp where emp_id = ? ");

        //4.执行,接收返回结果集
        preparedStatement.setInt(1,5);
        ResultSet resultSet = preparedStatement.executeQuery();

        //5.处理结果集
        while (resultSet.next()){
            int empId = resultSet.getInt("emp_id");
            String empName = resultSet.getString("emp_name");
            double empSalary = resultSet.getDouble("emp_salary");
            int empAge = resultSet.getInt("emp_age");
            System.out.println("empId:"+empId+"\t"+"empName:"+empName+"\t"+"empSalary:"+empSalary+"\t"+"empAge:"+empAge);
        }

        //6.释放资源（先开后关）
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }

    @Test
    public void queryMoreRow() throws Exception{
        //1.注册驱动

        //2.获取连接对象
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/atguigu", "root", "123456");

        //3.获取执行SQL语句的对象
        PreparedStatement preparedStatement = connection.prepareStatement("select emp_id,emp_name,emp_salary,emp_age from t_emp where emp_age > ? ");

        //4.执行,接收返回结果集
        preparedStatement.setInt(1,25);
        ResultSet resultSet = preparedStatement.executeQuery();

        //5.处理结果集
        while (resultSet.next()){
            int empId = resultSet.getInt("emp_id");
            String empName = resultSet.getString("emp_name");
            double empSalary = resultSet.getDouble("emp_salary");
            int empAge = resultSet.getInt("emp_age");
            System.out.println("empId:"+empId+"\t"+"empName:"+empName+"\t"+"empSalary:"+empSalary+"\t"+"empAge:"+empAge);
        }

        //6.释放资源（先开后关）
        resultSet.close();
        preparedStatement.close();
        connection.close();
    }

    @Test
    public void insertRow() throws Exception{
        //1.注册驱动

        //2.获取连接对象
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/atguigu", "root", "123456");

        //3.获取执行SQL语句的对象
        PreparedStatement preparedStatement = connection.prepareStatement("insert into t_emp(emp_name,emp_salary,emp_age) values (?,?,?)");

        //4.执行,接收返回结果集
        preparedStatement.setString(1,"rose");
        preparedStatement.setDouble(2,345.67);
        preparedStatement.setInt(3,28);

        int row = preparedStatement.executeUpdate();

        //5.处理结果集
        System.out.println(row);

        //6.释放资源（先开后关）
        preparedStatement.close();
        connection.close();
    }

    @Test
    public void updateRow() throws Exception{
        //1.注册驱动

        //2.获取连接对象
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/atguigu", "root", "123456");

        //3.获取执行SQL语句的对象
        PreparedStatement preparedStatement = connection.prepareStatement("update t_emp set emp_name = ?,emp_salary = ?,emp_age = ? where emp_id = ?");

        //4.执行,接收返回结果集
        preparedStatement.setString(1,"rose");
        preparedStatement.setDouble(2,345.67);
        preparedStatement.setInt(3,28);
        preparedStatement.setInt(4,6);

        int row = preparedStatement.executeUpdate();

        //5.处理结果集
        System.out.println(row);

        //6.释放资源（先开后关）
        preparedStatement.close();
        connection.close();
    }

    @Test
    public void deleteRow() throws Exception{
        //1.注册驱动

        //2.获取连接对象
        Connection connection = DriverManager.getConnection("jdbc:mysql://localhost:3306/atguigu", "root", "123456");

        //3.获取执行SQL语句的对象
        PreparedStatement preparedStatement = connection.prepareStatement("delete from t_emp where emp_id = ?");

        //4.执行,接收返回结果集
        preparedStatement.setInt(1,6);

        int row = preparedStatement.executeUpdate();

        //5.处理结果集
        System.out.println(row);

        //6.释放资源（先开后关）
        preparedStatement.close();
        connection.close();
    }
}
